package com.pks.datacenter.controller.basic;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang3.StringUtils;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSONObject;
import com.pks.datacenter.entity.basic.DbSource;
import com.pks.datacenter.service.basic.DbInfoService;
import com.pks.datacenter.service.basic.DbSourceService;
import com.pks.datacenter.utils.SUtilAes;
import com.pks.datacenter.utils.SUtilDb;
import com.pks.resultful.common.SRemoteResult;
import com.pks.util.SUtilQbuilder;
import com.pks.util.SUtilValidate;
import com.pks.util.UtilConvert;
import com.pks.web.springmvc.controller.BasicController;

@RestController
@RequestMapping(value = "/dbSource")
@Scope("prototype")
@Component("dbSourceController")
@Slf4j
@Api(tags = "数据库")
public class DbSourceController extends BasicController {

	@Resource
	private DbSourceService dbSourceService;
	@Resource
	private DbInfoService dbInfoService;
	
	@ApiOperation(value = "列表查询")
	@ApiImplicitParams({

			@ApiImplicitParam(name = "name", value = "数据库名称", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "nameLike", value = "数据库名称", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_type", value = "数据库类型 1-MYSQL  2-SQLSERVER 3-ORACLE", dataType = "Integer", paramType = "query"),
			@ApiImplicitParam(name = "db_ip", value = "数据库IP", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_ipLike", value = "数据库IP", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_name", value = "数据库库名", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_nameLike", value = "数据库库名", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_account", value = "数据库账号", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_accountLike", value = "数据库账号", dataType = "String", paramType = "query") })
	@RequestMapping(value = "/list", method = RequestMethod.POST)
	public SRemoteResult<List<DbSource>> list(HttpServletRequest request, String name,
			String nameLike, Integer db_type, String db_ip, String db_ipLike, String db_name,
			String db_nameLike, String db_account, String db_accountLike, Integer offset,
			Integer pagesize) {
		Map<String, Object> qbuilder = new HashMap<String, Object>();
		if (StringUtils.isNotBlank(name)) {
			qbuilder.put("name", name);
		}
		if (StringUtils.isNotBlank(nameLike)) {
			qbuilder.put("nameLike", "%" + nameLike.trim() + "%");
		}
		if (db_type != null) {
			qbuilder.put("db_type", db_type);
		}
		if (StringUtils.isNotBlank(db_ip)) {
			qbuilder.put("db_ip", db_ip);
		}
		if (StringUtils.isNotBlank(db_ipLike)) {
			qbuilder.put("db_ipLike", "%" + db_ipLike.trim() + "%");
		}
		if (StringUtils.isNotBlank(db_name)) {
			qbuilder.put("db_name", db_name);
		}
		if (StringUtils.isNotBlank(db_nameLike)) {
			qbuilder.put("db_nameLike", "%" + db_nameLike.trim() + "%");
		}
		if (StringUtils.isNotBlank(db_account)) {
			qbuilder.put("db_account", db_account);
		}
		if (StringUtils.isNotBlank(db_accountLike)) {
			qbuilder.put("db_accountLike", "%" + db_accountLike.trim() + "%");
		}
		List<DbSource> list = new ArrayList<DbSource>();
		int total = 0;
		total = dbSourceService.getTotalRows(qbuilder);
		if (total > 0) {
			list = dbSourceService.findByPage("utime desc", qbuilder);
			for (DbSource obj : list) {
				obj.setDb_password(null);
			}
		}
		return SRemoteResult.initPageList(list, total);
	}

	@ApiOperation(value = "新增")
	@RequestMapping(value = "/add", method = RequestMethod.POST)
	public SRemoteResult<String> add(HttpServletRequest request, DbSource obj) {
		if (obj == null) {
			return SRemoteResult.initErrorParamMissing(null);
		}
		SRemoteResult<String> validate = SUtilValidate.validate(obj);
		if (!validate.success()) {
			return validate;
		}
		try {
			SUtilDb.executeSql(obj.getDb_type(), obj.getDb_ip(), obj.getDb_port(), obj.getDb_name(), obj.getDb_account(), obj.getDb_password(), "select 1 ");
		} catch (SQLException e) {
			log.error("", e);
			return SRemoteResult.initError("数据库连接信息填写不正确，无法访问 sqlState:" + e.getSQLState());
		} catch (Exception e) {
			log.error("", e);
			return SRemoteResult.initError("数据库连接信息填写不正确，无法访问");
		}
		obj.insertBefore();
		obj.setDb_password(SUtilAes.Encrypt(obj.getDb_password()));
		dbSourceService.insert(obj);
		return SRemoteResult.initSuccess(null);
	}

	@ApiOperation(value = "修改")
	@RequestMapping(value = "/modify", method = RequestMethod.POST)
	public SRemoteResult<String> modify(HttpServletRequest request, DbSource obj) {
		if (obj == null || obj.getId() == null) {
			return SRemoteResult.initErrorParamMissing(null);
		}
		SRemoteResult<String> validate = SUtilValidate.validate(obj);
		if (!validate.success()) {
			return validate;
		}
		DbSource update = dbSourceService.findById(obj.getId());
		if (update == null) {
			return SRemoteResult.initErrorDataNone();
		}
		String password = obj.getDb_password();
		if ("******".equals(password)) {
			password = SUtilAes.Decrypt(update.getDb_password());
		}
		try {
			SUtilDb.executeSql(obj.getDb_type(), obj.getDb_ip(), obj.getDb_port(), obj.getDb_name(), obj.getDb_account(), password, "select 1 ");
		} catch (SQLException e) {
			e.printStackTrace();
			return SRemoteResult.initError("数据库连接信息填写不正确，无法访问 sqlState:" + e.getSQLState());
		} catch (Exception e) {
			e.printStackTrace();
			return SRemoteResult.initError("数据库连接信息填写不正确，无法访问");
		}
		obj.setDb_password(SUtilAes.Encrypt(password));
		dbSourceService.update(obj);
		return SRemoteResult.initSuccess(null);
	}

	@ApiOperation(value = "删除记录")
	@RequestMapping(value = "/delete", method = RequestMethod.POST)
	public SRemoteResult<String> delete(HttpServletRequest request, String ids) {
		if (StringUtils.isBlank(ids)) {
			return SRemoteResult.initErrorParamBlank(null);
		}
		if (StringUtils.isBlank(ids) || !ids.matches("^[0-9]+(,[0-9]+)*$")) {
			return SRemoteResult.initErrorParamInvalid(null);
		}
		List<Long> idList = UtilConvert.string2LongList(ids);
		if (dbInfoService.selectExists(SUtilQbuilder.initQbuilder("dbSourceIds", idList))) {
			return SRemoteResult.initError("要删除的数据已被使用，无法删除");
		}
		if (idList.size() == 1) {
			dbSourceService.delete(idList.get(0));
		} else {
			dbSourceService.deleteByIds(idList);
		}
		return SRemoteResult.initSuccess(null);
	}

	@ApiOperation(value = "获取详情")
	@ApiImplicitParams({ @ApiImplicitParam(name = "id", value = "id", dataType = "long", paramType = "query") })
	@RequestMapping(value = "/get", method = { RequestMethod.GET, RequestMethod.POST })
	public SRemoteResult<DbSource> findInfo(HttpServletRequest request, Long id) {
		if (id == null) {
			return SRemoteResult.initErrorParamMissing(null);
		}
		DbSource obj = dbSourceService.findById(id);
		if (obj == null) {
			return SRemoteResult.initErrorDataNone();
		}
		obj.setDb_password("******");
		return SRemoteResult.initSuccess(obj);
	}

	@ApiOperation(value = "数据查询（不分页）")
	@ApiImplicitParams({
			@ApiImplicitParam(name = "name", value = "数据库名称", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "nameLike", value = "数据库名称", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_type", value = "数据库类型 1-MYSQL  2-SQLSERVER 3-ORACLE", dataType = "Integer", paramType = "query"),
			@ApiImplicitParam(name = "db_ip", value = "数据库IP", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_ip", value = "数据库IP", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_name", value = "数据库库名", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_nameLike", value = "数据库库名", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_account", value = "数据库账号", dataType = "String", paramType = "query"),
			@ApiImplicitParam(name = "db_accountLike", value = "数据库账号", dataType = "String", paramType = "query") })
	@RequestMapping(value = "/data", method = RequestMethod.POST)
	public SRemoteResult<List<DbSource>> data(HttpServletRequest request, String name,
			String nameLike, Integer db_type, String db_ip, String db_ipLike, String db_name,
			String db_nameLike, String db_account, String db_accountLike) {
		Map<String, Object> qbuilder = new HashMap<String, Object>();
		if (StringUtils.isNotBlank(name)) {
			qbuilder.put("name", name);
		}
		if (StringUtils.isNotBlank(nameLike)) {
			qbuilder.put("nameLike", "%" + nameLike.trim() + "%");
		}
		if (db_type != null) {
			qbuilder.put("db_type", db_type);
		}
		if (StringUtils.isNotBlank(db_ip)) {
			qbuilder.put("db_ip", db_ip);
		}
		if (StringUtils.isNotBlank(db_ipLike)) {
			qbuilder.put("db_ipLike", "%" + db_ipLike.trim() + "%");
		}
		if (StringUtils.isNotBlank(db_name)) {
			qbuilder.put("db_name", db_name);
		}
		if (StringUtils.isNotBlank(db_nameLike)) {
			qbuilder.put("db_nameLike", "%" + db_nameLike.trim() + "%");
		}
		if (StringUtils.isNotBlank(db_account)) {
			qbuilder.put("db_account", db_account);
		}
		if (StringUtils.isNotBlank(db_accountLike)) {
			qbuilder.put("db_accountLike", "%" + db_accountLike.trim() + "%");
		}
		List<DbSource> list = new ArrayList<DbSource>();
		list = dbSourceService.find("utime desc", qbuilder, null);
		return SRemoteResult.initPageList(list, list.size());
	}
	
	@RequestMapping("/findDbTables")
	public SRemoteResult<List<JSONObject>> findDbTables(HttpServletRequest request, Long dbSourceId) {
		if (dbSourceId == null) {
			return SRemoteResult.initError("请选择数据库数据源");
		}
		DbSource dbSource = dbSourceService.findById(dbSourceId);
		if (dbSource == null) {
			return SRemoteResult.initError("数据源不存在");
		}
		dbSource.setDb_password(SUtilAes.Decrypt(dbSource.getDb_password()));
		String sql = "SELECT table_name, table_comment FROM information_schema.tables WHERE table_schema = '"+dbSource.getDb_name()+"' ORDER BY table_name;";
		List<JSONObject> queryList = SUtilDb.queryList(dbSource.getDb_type(), dbSource.getDb_ip(), dbSource.getDb_port(), dbSource.getDb_name(), dbSource.getDb_account(), dbSource.getDb_password(), sql);
		return SRemoteResult.initPageList(queryList, queryList.size());
	}

}
